USE [Daco]
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.ProductCategoryIDTable') AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT')))
	DROP FUNCTION dbo.ProductCategoryIDTable
GO
CREATE FUNCTION dbo.ProductCategoryIDTable
(
    @ParentCategoryID INT
)
RETURNS @ProductCategoryIDTable TABLE (ProductCategoryID INT) 
AS
BEGIN
	IF(@ParentCategoryID <= 0) 
		SET @ParentCategoryID = NULL;
		
	INSERT INTO @ProductCategoryIDTable(ProductCategoryID)
	VALUES (@ParentCategoryID)
	
	;WITH Categories(PCategoryID)
	AS
	(
		-- Anchor member definition
		SELECT c.PCategoryID
		FROM Store.ProductCategories c
		WHERE	((@ParentCategoryID IS NOT NULL) OR (c.PParentCategoryID IS NULL)) AND
				((@ParentCategoryID IS NULL) OR (c.PParentCategoryID = @ParentCategoryID))
				
		UNION ALL
		-- Recursive member definition
		SELECT c.PCategoryID
		FROM Store.ProductCategories c
		INNER JOIN Categories ON c.PParentCategoryID = Categories.PCategoryID
	)
	-- Statement that executes the CTE
	INSERT INTO @ProductCategoryIDTable(ProductCategoryID)
		SELECT PCategoryID
		FROM Categories
		
	RETURN
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.Split') AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT')))
	DROP FUNCTION dbo.Split
GO
CREATE FUNCTION dbo.Split
(
    @ItemList NVARCHAR(4000), 
    @delimiter CHAR(1)
)
RETURNS @IDTable TABLE (ItemId NVARCHAR(50))  
AS      

BEGIN    
    DECLARE @tempItemList NVARCHAR(4000)
    SET @tempItemList = @ItemList

    DECLARE @i INT    
    DECLARE @Item NVARCHAR(4000)

    SET @tempItemList = REPLACE (@tempItemList, ' ', '')
    SET @i = CHARINDEX(@delimiter, @tempItemList)

    WHILE (LEN(@tempItemList) > 0)
    BEGIN
        IF @i = 0
            SET @Item = @tempItemList
        ELSE
            SET @Item = LEFT(@tempItemList, @i - 1)
        INSERT INTO @IDTable(ItemId) VALUES(@Item)
        IF @i = 0
            SET @tempItemList = ''
        ELSE
            SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
        SET @i = CHARINDEX(@delimiter, @tempItemList)
    END 
    RETURN
END
GO